/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class TestSCompany {

    public static void main(String[] args) {
        Connection conn = null;
        Statement pstmt = null;
        Statement pstmt2 = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        Map<String, String> map = new HashMap<String, String>();
        try {
            conn = Main.getOracleConnection();
            String sql = "select id,ownerCode,investCountry,investmentCountry from company where etype='S' and investmentCountry is null";
            String sql2 = "select code,name from country";
            pstmt2 = conn.createStatement();
            rs2 = pstmt2.executeQuery(sql2);
            while (rs2.next()) {
                String code = rs2.getString("code");
                String name = rs2.getString("name");
                map.put(name.trim(), code.trim());
            }
            rs2.close();
            pstmt2.close();
//            for(Map.Entry<String, String> entry : map.entrySet()){
//                String name = entry.getKey();
//                String code = entry.getValue();
//                System.out.print("name = " + name);
//                System.out.println("  code = " + code);
//            }
            pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = pstmt.executeQuery(sql);
            while (rs.next()) {
                String id = rs.getString("id");
                String ownerCode = rs.getString("ownerCode");
                String investCountry = rs.getString("investCountry");
//                System.out.print("id = " + id);
//                System.out.print(" ownerCode = " + ownerCode);
//                System.out.println(" investCountry = " + investCountry);
                if (investCountry != null) {
                    String[] strs = investCountry.split(",");
                    if (strs[0] != null && !"".equals(strs[0])) {
                        if ("香港".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("中国香港"));
                            rs.updateRow();
                        } else if ("台湾省".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("台澎金马关税区"));
                            rs.updateRow();
                        }  else if ("澳门".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("中国澳门"));
                            rs.updateRow();
                        } else if ("俄罗斯".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("俄罗斯联邦"));
                            rs.updateRow();
                        } else if ("德意志联邦共和国".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("德国"));
                            rs.updateRow();
                        } else if ("马绍尔群岛共和国".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("马绍尔群岛"));
                            rs.updateRow();
                        }else if ("圣其茨--尼维斯".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("圣其茨-尼维斯"));
                            rs.updateRow();
                        }else if ("联合国和其他国际组织".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("联合国及机构和国际组织"));
                            rs.updateRow();
                        }else if ("国（地）别不详的".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("国(地)别不详的"));
                            rs.updateRow();
                        }else if ("斯洛文尼亚共和国".equals(strs[0])) {
                            rs.updateObject("investmentCountry", map.get("斯洛文尼亚"));
                            rs.updateRow();
                        }else {
                            if (map.get(strs[0]) != null && !"".equals(map.get(strs[0]))) {
                                rs.updateObject("investmentCountry", map.get(strs[0]));
                                rs.updateRow();
                            }
                        }
                    }
                }
//                for(String str : strs){
//                    System.out.println("str = "+str);
//                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            Logger.getLogger(TestSCompany.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
                pstmt.close();
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
                Logger.getLogger(TestSCompany.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
